<?php
	class Order_course_auths extends CI_Model{

	 	function __construct(){
          	parent::__construct();
      	}

        /**
        * 函数功能介绍：返回课程列表（用于分页显示）
        * 创建人:  Yang Ningshu
        * @param id int
        * @return array
        */
        function get_course_list($limit = 0, $offset = 10,$data = 0){
            $where = "data_type = 1 ";
            $order = "v.id desc";

            if(!empty($data['school_id']) ){
                $where .= " AND v.school_id = '". $data['school_id']."'";
            }

            if(!empty($data['name'])){
                $where .= " AND c.title LIKE '%". $data['name']."%'";
            }

            $sql = "SELECT SQL_CALC_FOUND_ROWS v.*,cc.school_name,c.title,count(vcc.id) AS useed FROM {$this->db->dbprefix}o2o_school_order_records as v left join {$this->db->dbprefix}o2o_school as cc on(v.school_id = cc.id) left join {$this->db->dbprefix}course as c on(v.data_id = c.id) LEFT JOIN {$this->db->dbprefix}o2o_school_system_video_course_codes AS vcc ON(vcc.record_id=v.id AND vcc.status=2) WHERE $where GROUP BY v.id ORDER BY ".$order." LIMIT $limit, $offset";
            $result = $this->db->query($sql)->result_array();

            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }

        function get_tiku_list($limit = 0, $offset = 10,$data = 0){
            $where = "data_type = 4 ";
            $order = "v.id desc";

            if(isset($data['school_id']) && $data['school_id'] > 0){
                $where .= " AND v.school_id = '". $data['school_id']."'";
            }

            if(!empty($data['name'])){
                $where .= " AND s.title LIKE '%". $data['name']."%'";
            }

            $sql = "SELECT SQL_CALC_FOUND_ROWS v.*,cc.school_name,s.title,count(vcc.id) AS useed FROM {$this->db->dbprefix}o2o_school_order_records as v left join {$this->db->dbprefix}o2o_school as cc on(v.school_id = cc.id) left join {$this->db->dbprefix}b_subject as s on(v.data_id = s.id) LEFT JOIN {$this->db->dbprefix}o2o_school_system_topic_codes AS vcc ON(vcc.record_id=v.id AND vcc.status=2) WHERE $where GROUP BY v.id ORDER BY ".$order." LIMIT $limit, $offset";
            $result = $this->db->query($sql)->result_array();

            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }

        function get_shixun_list($limit = 0, $offset = 10,$data = 0){
            $where = "data_type = 5 ";
            $order = "v.id desc";

            if(isset($data['school_id']) && $data['school_id'] > 0){
                $where .= " AND v.school_id = '". $data['school_id']."'";
            }

            $sql = "SELECT SQL_CALC_FOUND_ROWS v.*,cc.school_name,count(vcc.id) AS useed FROM {$this->db->dbprefix}o2o_school_order_records as v left join {$this->db->dbprefix}o2o_school as cc on(v.school_id = cc.id) LEFT JOIN {$this->db->dbprefix}o2o_school_system_shixun_codes AS vcc ON(vcc.record_id=v.id AND vcc.status=2) WHERE $where GROUP BY v.id ORDER BY ".$order." LIMIT $limit, $offset";
            $result = $this->db->query($sql)->result_array();

            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }

        function get_video_course_list($limit = 0, $offset = 10,$data = 0){
            $where = "data_type = 6 ";
            $order = "v.id desc";

            if(isset($data['school_id']) && $data['school_id'] > 0){
                $where .= " AND v.school_id = '". $data['school_id']."'";
            }

            $sql = "SELECT SQL_CALC_FOUND_ROWS v.*,cc.school_name,count(vcc.id) AS useed FROM {$this->db->dbprefix}o2o_school_order_records as v left join {$this->db->dbprefix}o2o_school as cc on(v.school_id = cc.id) LEFT JOIN {$this->db->dbprefix}o2o_school_system_shixun_codes AS vcc ON(vcc.record_id=v.id AND vcc.status=2) WHERE $where GROUP BY v.id ORDER BY ".$order." LIMIT $limit, $offset";
            $result = $this->db->query($sql)->result_array();

            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }


        function get_school(){
            $sql = "SELECT school_name,id from {$this->db->dbprefix}o2o_school where status = 1 and run_status = 0";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }

        function get_order_record_codes($record_id, $data_type){
            if($data_type == 4){
                $sql = "SELECT c.*,u.name,recode.expire_day FROM {$this->db->dbprefix}o2o_school_system_topic_codes AS c ";
            }elseif($data_type == 1){
                $sql = "SELECT c.*,u.name,recode.expire_day FROM {$this->db->dbprefix}o2o_school_system_video_course_codes AS c ";
            }elseif($data_type == 5){
                $sql = "SELECT c.*,u.name,recode.expire_day FROM {$this->db->dbprefix}o2o_school_system_shixun_codes AS c ";
            }elseif($data_type == 6){
                $sql = "SELECT c.*,u.name,recode.expire_day FROM {$this->db->dbprefix}o2o_school_video_user_codes AS c ";
            }
            $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_student AS u ON(u.user_id=c.use_uid) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_school_order_records AS recode ON(recode.id=c.record_id) ";
            $sql .= "WHERE c.record_id = $record_id";
            $result = $this->db->query($sql)->result_array();
            return $result; 
        }

        function get_order_record_list($limit= 0, $offset = 0, $conditions = array()){
            $where = " 1 = 1 ";//" order_type = $type ";
            if(!empty($conditions['school_id'])){
                $where .= " and r.school_id='{$conditions['school_id']}' ";
            }
            if(!empty($conditions['start_time'])){
                $start_time = strtotime($conditions['start_time']);
                $where .= " and r.add_time >= '{$start_time}' ";
            }
            if(!empty($conditions['end_time'])){
                $end_time = strtotime($conditions['end_time']) + 3600 * 24;
                $where .= " and r.add_time <= '{$end_time}' ";
            }
            $sql = "SELECT SQL_CALC_FOUND_ROWS r.*, school.school_name, subject.title, c.title AS ctitle, a.name AS agent_name FROM {$this->db->dbprefix}o2o_school_order_records AS r ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_school AS school ON(school.id = r.school_id) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}b_subject AS subject ON(subject.id = r.data_id) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}course AS c ON(c.id = r.data_id) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}agents AS a ON(a.id = school.agent_id) WHERE $where ORDER BY r.id DESC ";
            if($limit > 0 || $offset > 0){
                $sql .= "LIMIT $limit, $offset";
            }
            $result = $this->db->query($sql)->result_array();
            if($limit > 0 || $offset > 0){
                $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
                return array("count" => $row_num['row_num'], "result" => $result);
            }
            return $result; 
        }

        function get_order_record_amount($conditions = array()){
            $where = " 1 = 1 ";//" order_type = $type ";
            if(!empty($conditions['school_id'])){
                $where .= " and school_id='{$conditions['school_id']}' ";
            }
            if(!empty($conditions['start_time'])){
                $start_time = strtotime($conditions['start_time']);
                $where .= " and add_time >= '{$start_time}' ";
            }
            if(!empty($conditions['end_time'])){
                $end_time = strtotime($conditions['end_time']) + 3600 * 24;
                $where .= " and add_time <= '{$end_time}' ";
            }
            if(isset($conditions['commission_status'])){
                $where .= " and commission_status <= '{$conditions['commission_status']}' ";
            }
            $sql = "SELECT sum(price * num) AS total_amount, sum(commission) AS total_commission FROM {$this->db->dbprefix}o2o_school_order_records WHERE $where ";
            $result = $this->db->query($sql)->row_array();
            return $result; 
        }

        function duihuan_order_record_amount($conditions = array()){
            $where = " 1 = 1 ";//" order_type = $type ";
            if(!empty($conditions['school_id'])){
                $where .= " and school_id='{$conditions['school_id']}' ";
            }
            if(!empty($conditions['start_time'])){
                $start_time = strtotime($conditions['start_time']);
                $where .= " and add_time >= '{$start_time}' ";
            }
            if(!empty($conditions['end_time'])){
                $end_time = strtotime($conditions['end_time']) + 3600 * 24;
                $where .= " and add_time <= '{$end_time}' ";
            }
            if(isset($conditions['commission_status'])){
                $where .= " and commission_status <= '{$conditions['commission_status']}' ";
            }
            $sql = "UPDATE {$this->db->dbprefix}o2o_school_order_records SET commission_status = 1 WHERE $where ";
            $result = $this->db->query($sql);
        }
	}
